package com.ruoyi.epidemic.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 * @author wangrui
 * @Description: 内部excel使用工具
 * @date 2021/5/1316:06
 */
public class MyExcelUtil {

    public static final int XLS_MAX_ROW = 65535;

    /**
     * 初始化表头
     * @param wb
     * @param sheet
     * @param headers
     */
    public static void setHeaders(XSSFWorkbook wb, XSSFSheet sheet, List<String> headers) {
        //表头样式
        XSSFCellStyle style = wb.createCellStyle();
        //字体样式
        XSSFFont fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short) 12);
        style.setFont(fontStyle);
        style.setAlignment(HorizontalAlignment.CENTER);
        //生成sheet1内容
        XSSFRow rowFirst = sheet.createRow(0);
        //冻结第一行
        sheet.createFreezePane(0, 1, 0, 1);
        //写标题
        for (int i = 0; i < headers.size(); i++) {
            XSSFCell cell = rowFirst.createCell(i);
            sheet.setColumnWidth(i, 3000);
            cell.setCellStyle(style);
            cell.setCellValue(headers.get(i));
        }
    }

    /**
     * 设置单元格数据约束
     * @param sheet
     * @param constraint
     * @param promptText
     * @param errorText
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    public static void setColValidator(HSSFSheet sheet, DVConstraint constraint, String promptText, String errorText, int firstRow, int lastRow, int firstCol, int lastCol){
        // 设定在哪个单元格生效
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 创建规则对象
        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
        dataValidation.createPromptBox("", promptText);
        dataValidation.createErrorBox("", errorText);
        sheet.addValidationData(dataValidation);
    }

    /**
     * 设置指定列显示下拉数据
     * @param sheet
     * @param selectData 下拉框数据
     * @param firstRow   开始行
     * @param lastRow    结束行
     * @param firstCol   开始列
     * @param lastCol    结束列
     */
    public static void setColSelectData(HSSFSheet sheet, String[] selectData,int firstRow, int lastRow, int firstCol, int lastCol) {
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidationConstraint constraint = DVConstraint.createExplicitListConstraint(selectData);
        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(dataValidation);
    }

    /**
     * Excel文件下载
     * @param response
     * @param filename
     * @param wb
     */
    public static void commonExcelDownload(HttpServletResponse response, String filename, XSSFWorkbook wb) {
        OutputStream out = null;
        BufferedOutputStream bos = null;
        try {
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition","attachment;filename="+new String(filename.getBytes("utf-8"),"ISO8859-1"));
            out = response.getOutputStream();
            bos = new BufferedOutputStream(out);
            wb.write(out);
            bos.flush();
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
